package cn.com.longfuinfo.wx.service;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.aop.Before;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.longfuinfo.wx.common.OrderCode;
import cn.com.longfuinfo.wx.dao.Order;
import cn.com.longfuinfo.wx.kit.CommonKit;

public class OrderService {
	UserService userService = new UserService();
	
	/**
	 * 用户查询各种状态的订单列表
	 */
	public Page<Order> findByPage(int userid,int flag,int pageNum,int pageSize){
		if(flag==1){
			//1:全部，不显示“删除”的订单
			String sql = "FROM t_bus_order bo left join t_dict_store ds on bo.storeid = ds.storeid left join t_bus_goods bg on bo.goodsid=bg.id WHERE bo.userid=? AND bo.delState!=? ORDER BY bo.startDate DESC";
			return Order.me().paginate(pageNum, pageSize, "SELECT bo.*,ds.title",sql,userid,OrderCode.DelState.YES);
		}else if(flag==2){
			//2:待付款（“未支付”、）
			String sql = "FROM t_bus_order bo left join t_dict_store ds on bo.storeid = ds.storeid left join t_bus_goods bg on bo.goodsid=bg.id WHERE bo.userid=? AND bo.payState=? AND bo.delState!=? ORDER BY bo.startDate DESC";
			return Order.me().paginate(pageNum, pageSize, "SELECT bo.*,ds.title",sql,userid,OrderCode.OrderPayState.NO_PAY,OrderCode.DelState.YES);
		}else if(flag==3){
			//3：未兑换
			String sql = "FROM t_bus_order bo left join t_dict_store ds on bo.storeid = ds.storeid left join t_bus_goods bg on bo.goodsid=bg.id WHERE bo.userid=? AND  bo.payState=? AND bo.orderState=? and bo.tuistate=? AND bo.delState!=? ORDER BY bo.startDate DESC";
			return Order.me().paginate(pageNum, pageSize, "SELECT bo.*,ds.title",sql,userid,OrderCode.OrderPayState.HAD_PAY,OrderCode.OrderState.NO_CON,OrderCode.RefundState.ZERO,OrderCode.DelState.YES);
		}else if(flag==4){
			//4：已兑换
			String sql = "FROM t_bus_order bo left join t_dict_store ds on bo.storeid = ds.storeid left join t_bus_goods bg on bo.goodsid=bg.id WHERE bo.userid=? AND bo.payState=? AND bo.orderState=? AND bo.delState!=? ORDER BY bo.startDate DESC";
			return Order.me().paginate(pageNum, pageSize, "SELECT bo.*,ds.title",sql,userid,OrderCode.OrderPayState.HAD_PAY,OrderCode.OrderState.had_CON,OrderCode.DelState.YES);
		}else if(flag==5){
			//4：已过期
			String sql = "FROM t_bus_order bo  left join t_dict_store ds on bo.storeid = ds.storeid left join t_bus_goods bg on bo.goodsid=bg.id WHERE bo.userid=? AND bo.payState=? AND bo.orderState=? and bo.tuistate!=? AND bo.delState!=? ORDER BY bo.startDate DESC";
			return Order.me().paginate(pageNum, pageSize, "SELECT bo.*,ds.title",sql,userid,OrderCode.OrderPayState.HAD_PAY,OrderCode.OrderState.EXPIRE,OrderCode.RefundState.REFUNDED,OrderCode.DelState.YES);
		}
		return null;
	}
	
	/**
	 * 查询退款订单列表
	 */
	public Page<Order> findRefundByPage(String string, int userid, int pageNum, int pageSize) {
		String sql = "SELECT bor.id,bor.refundNum,bor.type,bor.orderNum,bor.orderListid,bor.amount,bor.state,bor.refuseReason,bor.createDate,bor.updateDate,bo.amount orderAmount";
		String sqlFrom = " FROM t_bus_order_refund bor INNER JOIN t_bus_order bo ON bor.orderNum = bo.orderNum AND (bo.storeid=? OR bo.isSelf=1) AND bo.userid = ? ORDER BY bor.createDate DESC";
		return Order.me().paginate(pageNum, pageSize, sql, sqlFrom, string, userid);
	}
	
	 
	/**
	 * 查询多个订单的商品列表
	 */
	public void getOrderGoodsList(List<Order> orderList){
		if(orderList!=null){
			//将所有订单的编号取出，以逗号分隔拼接
			String[] orderNumArr = new String[orderList.size()];
			for(int i=0;i<orderList.size();i++){
				orderNumArr[i] = "'"+orderList.get(i).getStr("orderNum")+"'";
			}
			//以订单编号为条件，查询所有订单的所有商品列表，并以订单号排序，方便遍历整理
			List<Record> allGoodsList = Db.find("SELECT orderNum,goodsid,name,price,thumb,spec,count,amount FROM t_bus_order_list WHERE orderNum IN ("+CommonKit.join(",", orderNumArr)+") ORDER BY orderNum");
			//遍历所有商品和订单，根据订单号将商品归类给对应的订单对象
			for(Order order : orderList){
				List<Record> goodsList = order.get("goodsList");
				if(goodsList==null){
					goodsList = new ArrayList<Record>();
				}
				for(Record goods : allGoodsList){
					if(order.getStr("orderNum").equals(goods.getStr("orderNum"))){
						goodsList.add(goods);
					}
				}
				order.put("goodsList",goodsList);
				order.put("statement",PropKit.get("constants.refund.statement"));
			}
		}
	}

 
	/**
	 * 根据用户ID和订单号查询出订单\n
	 * 排除已删除订单
	 */
	public Order getOrderByOrderNum(int userid,String orderNum){
		/* String sql = "SELECT bo.*,ds.title,ds.address,ds.tel as tell,cast(spec.marketPrice as char(11)) marketPrice,bg.goodtype FROM t_bus_order bo left join t_dict_store ds on bo.storeid=ds.storeid left join t_bus_goods bg on bo.goodsid=bg.id "
		 		+ "LEFT JOIN t_bus_goods_spec spec on spec.goodId=bg.id  WHERE bo.userid=? AND bo.orderNum=? AND bo.delState!=?";*/
		 String sql = "SELECT bo.*,ds.title,ds.address,ds.tel as tell,bg.goodtype FROM t_bus_order bo left join t_dict_store ds on bo.storeid=ds.storeid left join t_bus_goods bg on bo.goodsid=bg.id "
			 		+ "  WHERE bo.userid=? AND bo.orderNum=? AND bo.delState!=?";
		return Order.me().findFirst(sql,userid,orderNum,OrderCode.DelState.YES);
	}
	
	/***
	 * 查询商品类型
	 */
	/*public Integer getGoodsType(String orderNum){
		return Db.queryInt("select A.goodtype from t_bus_goods A where A.id=(select B.goodsid from t_bus_order B where B.orderNum= ?)",orderNum);
	}*/
	public Order getGoodsType(String orderNum){
		return Order.me().findFirst("select A.goodtype,A.type from t_bus_goods A where A.id=(select B.goodsid from t_bus_order B where B.orderNum= ?)",orderNum);
	}
	
	/**
	 * 根据订单号查询出订单\n
	 * 排除已删除、已过期的订单
	 */
	public Order getOrderByOrderNum(String orderNum){
		return Order.me().findFirst("SELECT bo.*,ds.title FROM t_bus_order bo LEFT JOIN t_dict_store ds ON bo.storeid=ds.storeid WHERE bo.orderNum=? AND bo.orderState!=? AND bo.delState!=?",orderNum,OrderCode.OrderState.EXPIRE,OrderCode.DelState.YES);
	}
	/**
	 * 根据订单号查询出订单\n
	 * 
	 */
	public Order getOrderByOrder(String orderNum){
		return Order.me().findFirst("SELECT bo.* FROM t_bus_order bo  WHERE bo.orderNum=? ",orderNum);
	}
	/**
	 * 自动退款
	 */
	public Order getOrderByOrderNums(String orderNum){
		return Order.me().findFirst("SELECT bo.* FROM t_bus_order bo  WHERE bo.orderNum=? AND bo.orderState=? ",orderNum,OrderCode.OrderState.EXPIRE);
	}
	/**
	 * 根据订单号查询订单操作日志
	 */
	public List<Record> getLogListByOrderNum(String orderNum){
		return Db.find("SELECT type,user,action,content,createDate FROM t_bus_order_log WHERE orderNum=? ORDER BY createDate DESC", orderNum);
	}
	
	
	/**
	 * 记录操作日志
	 */
	public void log(String orderNum, int type, String user, String action, String content){
		Db.update("INSERT INTO t_bus_order_log(orderNum,type,user,action,content) VALUES(?,?,?,?,?)",orderNum,type,user,action,content);
	}
	
	/**
	 * 订单评价
	 */
	public boolean doEval(String orderNum, int userid, int eval1, int eval2, String content){
		return Db.update("INSERT INTO t_bus_order_eval(orderNum,userid,eval1,eval2,content) VALUES(?,?,?,?,?)",orderNum,userid,eval1,eval2,content)==1;
	}
	
	 
	/**
	 * 取消订单，保存日志，并保存退款单信息
	 * @param order 订单信息
	 * @param reason 取消原因
	 * @param payType 支付类型
	 * @param payState 支付状态
	 * @param orderNum 订单编号
	 * @throws Exception 
	 */
	@Before(Tx.class)
/*	public Order cancelOrder(Order order,String reason, int payType, int payState, String orderNum) throws Exception {
		//4.取消订单操作，并记录日志
		order.set("orderState", OrderCode.OrderState.CANCEL).set("reason", reason);
		if(order.update()){
			//4-1.判断该订单是否是“在线支付”和“已支付”
			if(payType == OrderCode.OrderPayType.ON_LINE && payState == OrderCode.OrderPayState.HAD_PAY) {
				//保存退款单信息
				//生成退款类订单的订单编号，以大写字母T开头
				String orderRefundNum = "T"+DatetimeKit.getFormatDate("yyyyMMddHHmmssSSS")+CommonKit.randomNum(3);
				if(!new OrderRefund()
						.set("refundNum", orderRefundNum)
						.set("orderNum", orderNum)
						.set("type", OrderCode.RefundType.CANCEL_ORDER)
						.set("amount", order.getDouble("amount"))
						.save()){
					throw new Exception("生成退款单失败！");
				}
			}
			//4-2.订单取消成功，记录订单操作日志
			log(order.getStr("orderNum"),OrderCode.OrderLogType.CANCEL,"用户", OrderCode.OrderLogType.CANCEL_ACTION, "订单已取消");
			//4-3.查询订单的商品列表供页面展示
			order.put("goodsList", getGoodsListByOrderNum(order.getStr("orderNum")));
			return order;
		} else {
			throw new Exception("订单取消失败！");
		}
	}
	*/
	 

	public Order findOrderByEventKey(int eventKey) {
		return Order.me().findFirst("select * from t_bus_order where id=?",eventKey);
	}
	/**
	 * 未支付过期订单
	 * @param times
	 * @return
	 */
	public List<Order> findOrderList(String times) {
		return Order.me().find("select * from t_bus_order where payState=1 and delState =0 and expireDate < ?",times);
	}
	/**
	 * 未兑换过期订单
	 * @param times
	 * @return
	 */
	public List<Order> findOrderLists(String times) {
		return Order.me().find("select * from t_bus_order where payState=2 and orderState=1 and tuistate =0 and guoDate < ?",times);
	}
	/**
	 * 过期退款
	 * @param times
	 * @return
	 */
	public List<Order> findOrderListt(String times) {
		return Order.me().find("select * from t_bus_order where payState=2 and orderState=3 and tuistate =1 and guoDate < ?",times);
	}
	/**
	 * 3天未兑换过期订单提醒
	 * @param times
	 * @return
	 */
	public List<Order> findOrderLista(String times) { 
		return Order.me().find("select * from t_bus_order where payState=2 and orderState=1 and tuistate !=2 and date_add(guoDate, interval '3' day) = ?",times);
	}
	/**
	 * 兑换短信用到的，订单信息。
	 * @param OrderNum
	 * @return
	 */
	public Record orderMaessage(String OrderNum){
		return Db.findFirst("SELECT A.orderNum,A.tel ,A.goodsName ,T.name,B.title as store "
				+ "FROM t_bus_order A INNER JOIN t_dict_store B ON A.storeid = B.storeid INNER JOIN t_bus_seller C on B.sellerId=C.sellerId "
				+ "left JOIN t_bus_user_wx K on A.userid=K.id "
				+ "left join t_bus_user T on K.userid=T.id  where orderNum= ? " ,OrderNum);
	
	}
	/**
	 * 查询商品的类型：产前，产后。
	 */
	public Record FingFtype(String orderNum){
		String sql = "select B.ftype from t_bus_order A inner join t_bus_goods B on A.goodsid=B.goodsid where orderNum= ? ";
		return Db.findFirst(sql,orderNum);
	}
	/**
	 * 查询平台电话
	 */
	public List<Record> getBySellerPhone(int type){
		String sql = "SELECT phone FROM t_sys_sms where type=1 or type = ? ";
		return Db.find(sql,type);
	}
	/**
	 * 查询商家电话
	 */
	public List<Record> getBySellPhone(int sellerid){
		String sql = "SELECT phone FROM t_sys_sms where type=4 and sellerid=? ";
		return Db.find(sql,sellerid);
	}
	/**
	 * 查询店铺电话
	 */
	public List<Record> getByStorePhone(int storeid){
		String sql = "SELECT phone FROM t_sys_sms where type=5 and storeid=? ";
		return Db.find(sql,storeid);
	}
	public Record findStoreSellerId(String storeid) {
		return Db.findFirst("SELECT B.id,C.id as sellerid  from  t_dict_store B left join t_bus_seller C on B.sellerId=C.sellerId where B.storeid= ?",storeid);		
	}
}
